﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using DataLib.DBUtility;
using log4net;
using System.Reflection;

namespace DataLib.DAL
{
	/// <summary>
	/// 数据访问类:OutStorage
	/// </summary>
	public partial class OutStorage
	{

		#region  ExtensionMethod
        /// <summary>
        /// 待入库列表
        /// </summary>
        /// <returns></returns>
        public DataSet GetWaitInStorage(string where)
        {
            DataSet ds = new DataSet();
            string strsql = @"SELECT TOP 1000 OutStorage.id
      ,[outStorageNo]
      ,[outStorageDate]
      ,[outStorageType]
   
      ,[VLoadNum]
      ,OutStorage.status
 
      ,totalWeight
      ,[currentWeight]
      ,totalWeight - currentWeight as waitweight
  FROM [OutStorage]
  where OutStorage.status = 1 ";
            if (where != "")
                strsql += " and " + where;
            strsql += @" and (select COUNT(*) from BarCode where BarCode.OutStrorageID=OutStorage.id)>0
 order by id desc";
            ds = DbHelperSQL.Query(strsql);
            return ds;
        }


        /// <summary>
        /// 出库查询
        /// </summary>
        /// <param name="@Belong">归属地</param>
        /// <param name="@LoadNum">虚拟Load </param>
        /// <param name="@outNo">出库单号</param>
        /// <param name="@Graphic">处理地</param>
        /// <param name="@goodsCode">--物品编码</param>
        /// <param name="@StartDate">出库时间起</param>
        /// <param name="@EndDate">出库时间止 </param>
        /// <param name="@barvalue">条码号</param>
        /// <param name="@conman">领料人</param>
        /// <param name="@BoxID">-箱号（货盘号）</param>
        /// <param name="@cum">客户</param>
        /// <param name="@outType">--出库方式 2出库分类  1出库拆解 3出库生产 4 出库销售  5移库出库 0无此条件</param>
        /// <returns></returns>
        public DataTable getDtByUnionWhere(string Belong,
                    string LoadNum,
                    string outNo,
                    string Graphic,
                    string goodsCode,
                    string StartDate,
                    string EndDate,
                    string barvalue,
                    string conman,
                    string BoxID,
                    string cum,
                    string outtype,
                    string goodsname,
                    string storageno)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@Belong",SqlDbType.VarChar,10),
                new SqlParameter("@LoadNum",SqlDbType.VarChar,20),
                new SqlParameter("@outNo",SqlDbType.VarChar,15),
                new SqlParameter("@Graphic",SqlDbType.VarChar,10),
                new SqlParameter("@goodsCode",SqlDbType.VarChar,25),
                new SqlParameter("@StartDate",SqlDbType.DateTime),
                new SqlParameter("@EndDate",SqlDbType.DateTime),
                new SqlParameter("@barvalue",SqlDbType.VarChar,35),
                new SqlParameter("@conman",SqlDbType.VarChar,20),
                new SqlParameter("@BoxID",SqlDbType.VarChar,20),
                new SqlParameter("@cum",SqlDbType.VarChar,50),
                new SqlParameter("@outType",SqlDbType.VarChar,2),
                new SqlParameter("@goodsname",SqlDbType.VarChar,30),
                new SqlParameter("@storageno",SqlDbType.VarChar,20),
                new SqlParameter("@clearmonth",SqlDbType.VarChar,6)
                                         };
            sqlparameter[0].Value = Belong;
            sqlparameter[1].Value = LoadNum;
            sqlparameter[2].Value = outNo;
            sqlparameter[3].Value = Graphic;
            sqlparameter[4].Value = goodsCode;
            if (StartDate.Trim() != "" && EndDate.Trim() != "")
            {
                sqlparameter[5].Value = StartDate;
                sqlparameter[6].Value = EndDate;
            }
            sqlparameter[7].Value = barvalue;
            sqlparameter[8].Value = conman;
            sqlparameter[9].Value = BoxID;
            sqlparameter[10].Value = cum;
            sqlparameter[11].Value = outtype;
            sqlparameter[12].Value = goodsname;
            sqlparameter[13].Value = storageno;
            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_GetOutStorageItem", sqlparameter);
            dt.Load(dr);
            dr.Close();
            return dt;
        }


        /// <summary>
        /// 验证将要出库的BarCode是否合法
        /// </summary>
        /// <param name="barcodevalue"></param>
        /// <param name="outType"></param>
        /// <param name="returnvalue"></param>
        /// <returns></returns>
        public Model.BarCode OutBarCodeConfirm(string barcodevalue, string outType,string vload, ref string returnvalue)
        {
            Model.BarCode barModel = new Model.BarCode();
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@barcodevalue",SqlDbType.VarChar,35),
                new SqlParameter("@outType",SqlDbType.VarChar,2),
                new SqlParameter("@returnvalue",SqlDbType.VarChar,80),
                new SqlParameter("@vload",SqlDbType.VarChar,15)
             };
            sqlparameter[2].Direction = ParameterDirection.Output;

            sqlparameter[0].Value = barcodevalue;
            sqlparameter[1].Value = outType;
            sqlparameter[2].Value = returnvalue;
            sqlparameter[3].Value = vload;
             SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_OutStorageConfirm", sqlparameter);
            dt.Load(dr);
            dr.Close();
            returnvalue = sqlparameter[2].Value.ToString();
            if (dt != null && dt.Rows.Count > 0)
            {
                barModel = new BarCode().DataRowToModel(dt.Rows[0]);
            }
            else
            {
                return null;
            }

            return barModel;
        }

        /// <summary>
        /// 出库提交事件
        /// </summary>
        /// <param name="model">OutStorage的实例对象</param>
        /// <param name="strarr">需要出库的BarCode列表</param>
        /// <returns></returns>
        public int OutStorageSubmit(Model.OutStorage model, string strarr)
        {
            int rowcount;
            int result = 0;
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@arrBarCode",SqlDbType.VarChar,6000),
                new SqlParameter("@outid",SqlDbType.Int),
                new SqlParameter("@outno",SqlDbType.VarChar,12),
                new SqlParameter("@oper",SqlDbType.VarChar,20),
                new SqlParameter("@belong",SqlDbType.VarChar,20),
                new SqlParameter("@graphicCity",SqlDbType.VarChar,20),
                new SqlParameter("@ConsumMan",SqlDbType.VarChar,20),
                new SqlParameter("@outStorageType",SqlDbType.VarChar,3),
                new SqlParameter("@docusm",SqlDbType.VarChar,200),
                new SqlParameter("@saleno",SqlDbType.VarChar,3),
                new SqlParameter("@clearmonth",SqlDbType.VarChar,6)

             };
            sqlparameter[0].Value = strarr;
            sqlparameter[1].Value = model.id;
            sqlparameter[2].Value = model.outStorageNo;
            sqlparameter[3].Value = model.Operator;
            sqlparameter[4].Value = model.belogarea;
            sqlparameter[5].Value = model.graphicCity;
            sqlparameter[6].Value = model.ConsumMan;
            sqlparameter[7].Value = model.outStorageType;
            sqlparameter[8].Value = model.DoCustomer;
            sqlparameter[9].Value = model.OutsNo;
            sqlparameter[10].Value = model.clearmonth;
            result = DbHelperSQL.RunProcedure("EWMS_OutStorageSubmit", sqlparameter, out rowcount);
            return result;

        }


        /// <summary>
        /// 盘库出库提交事件
        /// </summary>
        /// <param name="model">OutStorage的实例对象</param>
        /// <param name="strarr">需要出库的BarCode列表</param>
        /// <returns></returns>
        public int PDOutStorageSubmit(Model.OutStorage model, string strarr)
        {
            int rowcount;
            int result = 0;
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@arrBarCode",SqlDbType.VarChar,6000),
                new SqlParameter("@outid",SqlDbType.Int),
                new SqlParameter("@outno",SqlDbType.VarChar,12),
                new SqlParameter("@oper",SqlDbType.VarChar,20)

             };
            sqlparameter[0].Value = strarr;
            sqlparameter[1].Value = model.id;
            sqlparameter[2].Value = model.outStorageNo;
            sqlparameter[3].Value = model.Operator;

            result = DbHelperSQL.RunProcedure("EWMS_PDOutStorageSubmit", sqlparameter, out rowcount);
            
            return result;

        }


        /// <summary>
        /// 出库修改提交事件
        /// </summary>
        /// <param name="model">OutStorage的实例对象</param>
        /// <param name="strarr">需要出库的BarCode列表</param>
        /// <returns></returns>
        public int OutStorageUpdate(Model.OutStorage model, string strarr)
        {
            BarCode b = new BarCode();
            
            int rowcount;
            int result = 0;

            string arrvalue = "";
            if (strarr != null && strarr.Trim().Length > 2)
            {

                string[] ids = strarr.Split(',');

                for (int i = 0; i < ids.Length; i++)
                {
                    if (ids[i] != null && ids[i].Trim() != "" && Common.PageValidate.IsNumber(ids[i]))
                    {
                        var m = b.GetModel(int.Parse(ids[i]));
                        if (m != null)
                            arrvalue += m.barcodevalue + ",";
                    }

                }
            }
            else
            {
                arrvalue = strarr;
            }
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@arrBarCode",SqlDbType.VarChar,6000),
                new SqlParameter("@outid",SqlDbType.Int),
                new SqlParameter("@outno",SqlDbType.VarChar,12),
                new SqlParameter("@oper",SqlDbType.VarChar,20),
                new SqlParameter("@belong",SqlDbType.VarChar,20),
                new SqlParameter("@graphicCity",SqlDbType.VarChar,20),
                new SqlParameter("@ConsumMan",SqlDbType.VarChar,20),
                new SqlParameter("@outStorageType",SqlDbType.VarChar,3),
                new SqlParameter("@docusm",SqlDbType.VarChar,200),
                new SqlParameter("@saleno",SqlDbType.VarChar,3),
                new SqlParameter("@clearmonth",SqlDbType.VarChar,6),
                new SqlParameter("@memo",SqlDbType.VarChar,200)

             };
            sqlparameter[0].Value = arrvalue;
            sqlparameter[1].Value = model.id;
            sqlparameter[2].Value = model.outStorageNo;
            sqlparameter[3].Value = model.Operator;
            sqlparameter[4].Value = model.belogarea;
            sqlparameter[5].Value = model.graphicCity;
            sqlparameter[6].Value = model.ConsumMan;
            sqlparameter[7].Value = model.outStorageType;
            sqlparameter[8].Value = model.DoCustomer;
            sqlparameter[9].Value = model.OutsNo;
            sqlparameter[10].Value = model.clearmonth;
            sqlparameter[11].Value = model.memo ;
            result = DbHelperSQL.RunProcedure("EWMS_OutStorageUpdate", sqlparameter, out rowcount);
            return result;

        }

        /// <summary>
        /// 出库条码删除事件
        /// </summary>
        /// <param name="barvalue">条码号</param>
        /// <param name="username">操作人员</param>
        /// <param name="outid">出库单ID</param>
        /// <returns></returns>
        public int DelBarOut(string barvalue,string username,string outid)
        {
            int rowcount;
            int result = 0;
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@barcodevalue",SqlDbType.VarChar,35),
                new SqlParameter("@operman",SqlDbType.VarChar,20),
                new SqlParameter("@outid",SqlDbType.Int)
              };
            sqlparameter[0].Value = barvalue;
            sqlparameter[1].Value = username;
            sqlparameter[2].Value = int.Parse(outid);

            result = DbHelperSQL.RunProcedure("EWMS_DelOutBar", sqlparameter, out rowcount);
            return result;
        }

        /// <summary>
        /// 计算barcode已入库的重量
        /// </summary>
        /// <returns></returns>
        public decimal GetInNwtByOutid(string outid)
        {
            DataTable dt = new DataTable();
            string strsql = @"select sum(nwt) from barcode where ParmentID=" + outid;
            dt = DbHelperSQL.Query(strsql).Tables[0];
            decimal curnwt = 0;
            if (dt != null && dt.Rows.Count > 0 && dt.Rows[0][0] != null && dt.Rows[0][0].ToString().Trim()!="")
            {
                curnwt = decimal.Parse(dt.Rows[0][0].ToString());
            }
            return curnwt;
        }


        /// <summary>
        /// 返回出库盘点ID
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public int InsertPDNo(string bcity, string gcity, string operman,string memo)
        {
            SqlParameter[] parameters = {
					new SqlParameter("@bcity", SqlDbType.NVarChar,20),
					new SqlParameter("@gcity", SqlDbType.NVarChar,20),
					new SqlParameter("@operman", SqlDbType.VarChar,20),
                    new SqlParameter("@memo", SqlDbType.VarChar,300)
                                        };

            parameters[0].Value = bcity;
            parameters[1].Value = gcity;
            parameters[2].Value = operman;
            parameters[3].Value = memo;
            DataTable dt = new DataTable();

            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_PDNoAdd", parameters);
            dt.Load(dr);
            dr.Close();

            int insid = 0;
            if (dt != null && dt.Rows.Count > 0)
            {
                try
                {
                    insid = int.Parse(dt.Rows[0][0].ToString());
                }
                catch
                {
                    insid = 0;
                }
            }
            else
            {
                insid = 0;
            }
            return insid;

        }


        /// <summary>
        /// 盘库出库列表
        /// </summary>
        /// <param name="city">处理地</param>
        /// <param name="status">7为待出库，8为待入库</param>
        /// <returns></returns>
        public DataTable GetPDOutsList(string city,string status)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@gcity",SqlDbType.VarChar,20),
                new SqlParameter("@status",SqlDbType.Int)
                                         };
            sqlparameter[0].Value = city;
            sqlparameter[1].Value = status;
            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_GetPDOutsList", sqlparameter);
            dt.Load(dr);
            dr.Close();
            return dt;
        }

        /// <summary>
        /// 盘库入库维护列表
        /// </summary>
        /// <param name="city">处理地</param>
        /// <returns></returns>
        public DataTable GetPDInsUpdateList(string city)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@gcity",SqlDbType.VarChar,20),
                                         };
            sqlparameter[0].Value = city;
            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_GetPDInsUpdateList", sqlparameter);
            dt.Load(dr);
            dr.Close();
            return dt;
        }


        /// <summary>
        /// 生成虚拟load
        /// </summary>
        /// <param name="outid"></param>
        public int? CreateVLoad(int outid)
        {
            int? id = 0;
            Model.OutStorage mouts = new OutStorage().GetModel(outid);
            StringBuilder strSql = new StringBuilder();
            DataTable dt = null;
            Model.Vload vload = new Model.Vload(); ;

            if (mouts != null)
            {

                if (mouts.outStorageType != "4")        //销售出库不生成虚拟load
                {

                    dt = DbHelperSQL.Query("select * from BarCode where OutStrorageID=" + mouts.id.ToString()).Tables[0];
                    if (dt != null && dt.Rows.Count > 0)
                    {
                        for (int i = 0; i < dt.Rows.Count; i++)
                        {

                        }

                        if (mouts.outStorageType == "2")
                        {
                        }

                    }
                }
            }
            return id;
        }
        public DataTable GetOutStorageListStore(string bcity, string gcity, int currpage, int length, out int totalcount, string search)
        {
            DataTable dt = new DataTable();
            IDataParameter[] sqlparameter ={
                new SqlParameter("@bcity",SqlDbType.VarChar,20),
                new SqlParameter("@gcity",SqlDbType.VarChar,20),
                new SqlParameter("@currpage",SqlDbType.Int,4),
                new SqlParameter("@Length",SqlDbType.Int,4),
                new SqlParameter("@serach",SqlDbType.VarChar,155),
                new SqlParameter("@totalcount",SqlDbType.Int,4)
                                         };
            sqlparameter[0].Value = bcity;
            sqlparameter[1].Value = gcity;
            sqlparameter[2].Value = currpage;
            sqlparameter[3].Value = length;
            sqlparameter[4].Value = search;
            sqlparameter[5].Direction = ParameterDirection.Output;
            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_OutStorageListStore", sqlparameter);
            dt.Load(dr);
            dr.Close();
            totalcount = Convert.ToInt32(sqlparameter[5].Value);
            return dt;
        }


		#endregion  ExtensionMethod
	}
}

